package net.groupme.model.dao;

import java.sql.Types;

import net.groupme.model.ObjectFactoryProperties;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.object.SqlUpdate;

public class SQLDBController {

	// table
	public static final String TABLE_USER = DBProperties
			.getString("GroupmeDB.TABLE_USER"); //$NON-NLS-1$

	public static final String USER_ID = DBProperties
			.getString("GroupmeDB.USER_ID"); //$NON-NLS-1$

	// table
	public static final String TABLE_USERTAGGROUPRESOURCE = DBProperties
			.getString("GroupmeDB.TABLE_USERTAGGROUPRESOURCE"); //$NON-NLS-1$

	// columns
	public static final String USERTAGGROUPRESOURCE_TAGID = DBProperties
			.getString("GroupmeDB.USERTAGGROUPRESOURCE_TAGID"); //$NON-NLS-1$

	public static final String USERTAGGROUPRESOURCE_USERID = DBProperties
			.getString("GroupmeDB.USERTAGGROUPRESOURCE_USERID"); //$NON-NLS-1$

	public static final String USERTAGGROUPRESOURCE_RESOURCEID = DBProperties
			.getString("GroupmeDB.USERTAGGROUPRESOURCE_RESOURCEID"); //$NON-NLS-1$
	
	public static final String USERTAGGROUPRESOURCE_GROUPID = DBProperties
			.getString("GroupmeDB.USERTAGGROUPRESOURCE_GROUPID"); //$NON-NLS-1$
	
	// table
	public static final String TABLE_GROUPRESOURCE = DBProperties
			.getString("GroupmeDB.TABLE_GROUPRESOURCE"); //$NON-NLS-1$

	// columns
	public static final String GROUPRESOURCE_GROUPID = DBProperties
			.getString("GroupmeDB.GROUPRESOURCE_GROUPID"); //$NON-NLS-1$

	public static final String GROUPRESOURCE_RESOURCEID = DBProperties
			.getString("GroupmeDB.GROUPRESOURCE_RESOURCEID"); //$NON-NLS-1$

	// table
	public static final String TABLE_USERGROUP = DBProperties
			.getString("GroupmeDB.TABLE_USERGROUP"); //$NON-NLS-1$

	// columns
	public static final String USERGROUP_USER_ID = DBProperties
			.getString("GroupmeDB.USERGROUP_USER_ID"); //$NON-NLS-1$

	public static final String USERGROUP_GROUP_ID = DBProperties
			.getString("GroupmeDB.USERGROUP_GROUP_ID"); //$NON-NLS-1$

	// table
	public static final String TABLE_USERGROUPVISUALIZATION = DBProperties
			.getString("GroupmeDB.TABLE_USERGROUPVISUALIZATION"); //$NON-NLS-1$

	// columns
	public static final String USERGROUPVISUALIZATION_VISUALIZATION_ID = DBProperties
			.getString("GroupmeDB.USERGROUPVISUALIZATION_VISUALIZATION_ID"); //$NON-NLS-1$

	public static final String USERGROUPVISUALIZATION_USER_ID = DBProperties
			.getString("GroupmeDB.USERGROUPVISUALIZATION_USER_ID"); //$NON-NLS-1$

	public static final String USERGROUPVISUALIZATION_GROUP_ID = DBProperties
			.getString("GroupmeDB.USERGROUPVISUALIZATION_GROUP_ID"); //$NON-NLS-1$
	
	// table
	public static final String TABLE_RESOURCE = DBProperties
			.getString("GroupmeDB.TABLE_RESOURCE"); //$NON-NLS-1$

	// columns
	public static final String RESOURCE_ID = DBProperties
			.getString("GroupmeDB.RESOURCE_ID"); //$NON-NLS-1$

	//	 table
	public static final String TABLE_TAG = DBProperties
			.getString("GroupmeDB.TABLE_TAG"); //$NON-NLS-1$

	// columns
	public static final String TAG_ID = DBProperties
			.getString("GroupmeDB.TAG_ID"); //$NON-NLS-1$

	//	 table
	public static final String TABLE_VISUALIZATION = DBProperties
			.getString("GroupmeDB.TABLE_VISUALIZATION"); //$NON-NLS-1$

	// columns
	public static final String VISUALIZATION_ID = DBProperties
			.getString("GroupmeDB.VISUALIZATION_ID"); //$NON-NLS-1$

	//	 table
	public static final String TABLE_VISUALIZATIONGROUPSTYLE = DBProperties
			.getString("GroupmeDB.TABLE_VISUALIZATIONGROUPSTYLE"); //$NON-NLS-1$

	// columns
	public static final String VISUALIZATIONGROUPSTYLE_VISUALIZATIONID = DBProperties
			.getString("GroupmeDB.VISUALIZATIONGROUPSTYLE_VISUALIZATIONID"); //$NON-NLS-1$

	//	 table
	public static final String TABLE_RESOURCEVISUALIZATION = DBProperties
			.getString("GroupmeDB.TABLE_RESOURCEVISUALIZATION"); //$NON-NLS-1$

	// columns
	public static final String RESOURCEVISUALIZATION_ID = DBProperties
			.getString("GroupmeDB.RESOURCEVISUALIZATION_ID"); //$NON-NLS-1$
	
	public static final String RESOURCEVISUALIZATION_VISUALIZATIONID = DBProperties
	.getString("GroupmeDB.RESOURCEVISUALIZATION_VISUALIZATIONID"); //$NON-NLS-1$
	
	/** Name of the Table that stores a Statement */
	protected static final String TABLE_METADATA = DBProperties
			.getString("GroupmeDB.table.Metadata"); //$NON-NLS-1$
	
	/** Name of the attribute which defines the Foreign Key to the Resource which is the 
	 * subject of a Statement */
	protected static final String METADATA_RESOURCE_ID = DBProperties
	.getString("GroupmeDB.Metadata.resourceID"); //$NON-NLS-1$
	
	/** the dirver managed data source which performs the removing actions. */
	private static DriverManagerDataSource ds = new DriverManagerDataSource();
	static {
		ds.setDriverClassName(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_DRIVER")); 
		ds.setUrl(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_URL")); 
		ds.setUsername(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_USER"));
		ds.setPassword(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_PW"));
	}
	
	/** Logger for this class and subclasses */
	@SuppressWarnings("unused")
	private static final Logger log = Logger.getLogger(UserDAOImpl.class);
	
	/**
	 * Removes a User from the table User by ID.
	 * Warning: All entries associated with the User in the tables UserGroup,
	 * UserGroupVisualization and UserTagGroupResource will be deleted, too.
	 * @param id the UserID
	 * @return int number of rows deleted
	 */
	public static int removeUserById(String id){
		int count = 0;
		count += removeUserByIdFromTableUserGroup(id, ds);
		count += removeUserByIdFromTableUserGroupVisualisation(id, ds);
		count += removeUserByIdFromTableUserTagGroupResource(id, ds);
		SqlUpdate su = new SqlUpdate(ds, "DELETE FROM " + TABLE_USER + " WHERE "	+ USER_ID + " = ? "); 
		su.declareParameter(new SqlParameter("uID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		count += su.update(oa);
		return count;
	}
	
	/**
	 * Removes a User from the table UserGroup by ID.
	 * @param id the UserID
	 * @return int number of rows deleted
	 */
	private static int removeUserByIdFromTableUserGroup(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_USERGROUP + " WHERE " + USERGROUP_USER_ID + " = ? "); 
		su.declareParameter(new SqlParameter("uID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a User from the table UserGroupVisualization by ID.
	 * @param id the UserID
	 * @return int number of rows deleted
	 */
	private static int removeUserByIdFromTableUserGroupVisualisation(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_USERGROUPVISUALIZATION + " WHERE " + USERGROUPVISUALIZATION_USER_ID + " = ? "); 
		su.declareParameter(new SqlParameter("uID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a User from the table UserTagGroupResource by ID.
	 * @param id the UserID
	 * @return int number of rows deleted
	 */
	private static int removeUserByIdFromTableUserTagGroupResource(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_USERTAGGROUPRESOURCE + " WHERE " + USERTAGGROUPRESOURCE_USERID + " = ? "); 
		su.declareParameter(new SqlParameter("uID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Resource from the table Resource by ID.
	 * Warning: All entries associated with the Resource in the tables TableGroupResource,
	 * Metadata and UserTagGroupResource will be deleted, too.
	 * @param id the ResourceID
	 * @return int number of rows deleted
	 */
	public static int removeResourceById(String id){
		int count = 0;
		count += removeResourceByIdFromTableMetadata(id, ds);
		count += removeResourceByIdFromTableGroupResource(id, ds);
		count += removeResourceByIdFromTableUserTagGroupResource(id, ds);
		SqlUpdate su = new SqlUpdate(ds, "DELETE FROM " + TABLE_RESOURCE + " WHERE " + RESOURCE_ID + " = ? "); 
		su.declareParameter(new SqlParameter("rID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		count += su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Resource from the table GroupResource by ID.
	 * @param id the ResourceID
	 * @return int number of rows deleted
	 */
	private static int removeResourceByIdFromTableGroupResource(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_GROUPRESOURCE + " WHERE " + GROUPRESOURCE_RESOURCEID + " = ? "); 
		su.declareParameter(new SqlParameter("rID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Resource from the table UserTagGroupResource by ID.
	 * @param id the ResourceID
	 * @return int number of rows deleted
	 */
	private static int removeResourceByIdFromTableUserTagGroupResource(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_USERTAGGROUPRESOURCE + " WHERE " + USERTAGGROUPRESOURCE_RESOURCEID + " = ? "); 
		su.declareParameter(new SqlParameter("rID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	private static int removeResourceByIdFromTableMetadata(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_METADATA + " WHERE " + METADATA_RESOURCE_ID + " = ? "); 
		su.declareParameter(new SqlParameter("rID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Group from the table Group by ID. 
	 * Warning: All entries associated with the Group in the tables TableGroupResource,
	 * TableUserGroup, UserGroupVisualization and UserTagGroupResource will be deleted, too.
	 * Removes the Resource associated with the Group and all references to the Resource, too.
	 * @param id the GroupID
	 * @return int number of rows deleted
	 */
	public static int removeGroupById(String id){
		int count = 0;
		count += removeGroupByIdFromTableGroupResource(id, ds);
		count += removeGroupByIdFromTableUserGroup(id, ds);
		count += removeGroupByIdFromTableUserGroupVisualization(id, ds);
		count += removeGroupByIdFromTableUserTagGroupResource(id, ds);
		count += removeResourceById(id);
		log.info("Remove affected " + count + " rows.");
		return count;
	}
	
	/**
	 * Removes a Group from the table GroupResource by ID.
	 * @param id the GroupID
	 * @return int number of rows deleted
	 */
	private static int removeGroupByIdFromTableGroupResource(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_GROUPRESOURCE + " WHERE " + GROUPRESOURCE_GROUPID + " = ? "); 
		su.declareParameter(new SqlParameter("gID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Group from the table UserGroup by ID.
	 * @param id the GroupID
	 * @return int number of rows deleted
	 */
	private static int removeGroupByIdFromTableUserGroup(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_USERGROUP + " WHERE " + USERGROUP_GROUP_ID + " = ? "); 
		su.declareParameter(new SqlParameter("gID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Group from the table GroupResource by ID.
	 * @param id the GroupID
	 * @return int number of rows deleted
	 */
	private static int removeGroupByIdFromTableUserGroupVisualization(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_USERGROUPVISUALIZATION + " WHERE " + USERGROUPVISUALIZATION_GROUP_ID + " = ? "); 
		su.declareParameter(new SqlParameter("gID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Group from the table GroupResource by ID.
	 * @param id the GroupID
	 * @return int number of rows deleted
	 */
	private static int removeGroupByIdFromTableUserTagGroupResource(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_USERTAGGROUPRESOURCE + " WHERE " + USERTAGGROUPRESOURCE_GROUPID + " = ? "); 
		su.declareParameter(new SqlParameter("gID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Tag from the table Tag by ID.
	 * Warning: All entries associated with the Tag in the table UserTagGroupResource
	 * will be deleted, too.
	 * @param id the TagID
	 * @return int number of rows deleted
	 */
	public static int removeTagById(String id){
		int count = 0;
		count += removeTagByIdFromTableUserTagGroupResource(id, ds);
		SqlUpdate su = new SqlUpdate(ds, "DELETE FROM " + TABLE_TAG + " WHERE " + TAG_ID + " = ? "); 
		su.declareParameter(new SqlParameter("tID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		count += su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Tag from the table UserTagGroupResource by ID.
	 * @param id the TagID
	 * @return int number of rows deleted
	 */
	private static int removeTagByIdFromTableUserTagGroupResource(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_USERTAGGROUPRESOURCE + " WHERE " + USERTAGGROUPRESOURCE_TAGID + " = ? "); 
		su.declareParameter(new SqlParameter("tID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Visualization from the table Visualization by ID.
	 * Warning: All entries associated with the Visualization in the tables UserGroupVisualization,
	 * VisualizationGroupStyle and ResourceVisualization will be deleted, too.
	 * @param id the VisualizationID
	 * @return int number of rows deleted
	 */
	public static int removeVisualizationById(String id){
		int count = 0;
		count += removeVisualizationByIdFromTableUserGroupVisualization(id, ds);
		count += removeVisualizationByIdFromTableVisualizationGroupStyle(id, ds);
		count += removeVisualizationByIdFromTableResourceVisualization(id, ds);
		SqlUpdate su = new SqlUpdate(ds, "DELETE FROM " + TABLE_VISUALIZATION + " WHERE " + VISUALIZATION_ID + " = ? "); 
		su.declareParameter(new SqlParameter("vID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		count += su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Visualization from the table UserGroupVisualization by ID.
	 * @param id the VisualizationID
	 * @return int number of rows deleted
	 */
	private static int removeVisualizationByIdFromTableUserGroupVisualization(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_USERGROUPVISUALIZATION + " WHERE " + USERGROUPVISUALIZATION_VISUALIZATION_ID + " = ? "); 
		su.declareParameter(new SqlParameter("vID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Visualization from the table VisualizationGroupStyle by ID.
	 * @param id the VisualizationID
	 * @return int number of rows deleted
	 */
	private static int removeVisualizationByIdFromTableVisualizationGroupStyle(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_VISUALIZATIONGROUPSTYLE + " WHERE " + VISUALIZATIONGROUPSTYLE_VISUALIZATIONID + " = ? "); 
		su.declareParameter(new SqlParameter("vID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a Visualization from the table VisualizationGroupStyle by ID.
	 * @param id the VisualizationID
	 * @return int number of rows deleted
	 */
	private static int removeVisualizationByIdFromTableResourceVisualization(String id, DriverManagerDataSource datasource){
		SqlUpdate su = new SqlUpdate(datasource, "DELETE FROM " + TABLE_RESOURCEVISUALIZATION + " WHERE " + RESOURCEVISUALIZATION_VISUALIZATIONID+ " = ? "); 
		su.declareParameter(new SqlParameter("vID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes a ResourceVisualization from the table ResourceVisualization by ID.
	 * @param id the ResourceVisualizationID
	 * @return int number of rows deleted
	 */
	public static int removeResourceVisualizationByIdFromTableResourceVisualization(String id){
		SqlUpdate su = new SqlUpdate(ds, "DELETE FROM " + TABLE_RESOURCEVISUALIZATION + " WHERE " + RESOURCEVISUALIZATION_ID + " = ? "); 
		su.declareParameter(new SqlParameter("rvID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		int count = su.update(oa);
		return count;
	}
	
	/**
	 * Removes the row identified via <i>id</i> from a table
	 * @param id the ID of the data set (row)
	 * @param idFieldName the id field name (should be <i>id</i> in most cases)
	 * @return int number of rows deleted (should be 0 or 1)
	 */
	public static int removeRowById(String table, String idFieldName, String id){
		int count = 0;
		SqlUpdate su = new SqlUpdate(ds, "DELETE FROM " + table + " WHERE " + RESOURCE_ID + " = ? "); 
		su.declareParameter(new SqlParameter("rID", Types.INTEGER)); 
		su.compile();
		Object[] oa = new Object[1];
		oa[0] = new Integer(id);
		count += su.update(oa);
		return count;
	}
}
